# -*- coding: utf-8 -*-
import datetime
import sys
import os
import xlsxwriter
import ibm_db
import logging

#文件格式如下：
#20200816-20200822 
#SELECT name FROM CMR.CMR WHERE date='{}'
#SELECT value FROM CMR.CMR WHERE date='{}'


# 设置时间格式20200816
def date_range(file_path,file_name):
    with open(os.path.join(file_path, file_name)) as f:
        datestr = f.readline()
        begin_date=datestr.split('-')[0]
        end_date=datestr.split('-')[1]
    dates = []
    dt = datetime.datetime.strptime(begin_date, '%Y%m%d')
    date = begin_date
    while date <= end_date:
        dates.append(date)
        dt = dt + datetime.timedelta(1)
        date = dt.strftime('%Y%m%d')
    return dates

#获取sql清单
def sql_info(file_path,file_name):
    with open(os.path.join(file_path, file_name)) as f:
        sql_info=[]
        #读两次，跳过第一行
        sql=f.readline()
        sql=f.readline()
        while (sql!=""):
            sql_info.append(sql.split("#")[1])
            sql=f.readline()
        return sql_info

#获取sheet清单
def sheet_list(file_path,file_name):
    with open(os.path.join(file_path, file_name)) as f:
        sheet_list=[]
        #读两次，跳过第一行
        sheet=f.readline()
        sheet=f.readline()
        while (sheet!=""):
            sheet_list.append(sheet.split("#")[0])
            sheet=f.readline()
        return sheet_list

# 读取sql语句，并传入时间范围参数，传入时间格式为20200816
def sql_list(sql,dates):
    sql_list=[]
    for i in range(len(dates)):
        sql_list.append(sql.format(dates[i]))
    return sql_list



# 数据库查询
def getres(sql):
    one_list=[]
    logging.basicConfig(level=logging.INFO)
    dbinfo = "DATABASE=testdb; HOSTNAME=127.0.0.1; PORT=55000; PROTOCOL=TCPIP; UID=pcsw_usr; PWD=1qaz@WSX;"
    #sql = sql_info()[1].strip()
    try:
        conn = ibm_db.connect(dbinfo, '', '')
    except Exception as err:
        print("connect failed {}".format(repr(err)))
    else:
        logging.info(sql)
        try:
            stmt = ibm_db.exec_immediate(conn, sql)
            result = ibm_db.fetch_both(stmt)
            while result:
                one_list.append(result[0])
                result = ibm_db.fetch_both(stmt)
            return one_list
        except Exception as err:
            print("stmt failed {}".format(repr(err)))
        finally:
            if ibm_db.active(conn):
                ibm_db.close(conn)
            logging.info("connect success close")
    


# 数据写入Excel
def w_excel(workbook,first_row,all_list,file_path,output_file,sheet_name):
    #if not os.path.exists(os.path.expanduser('~')+os.sep+"docs"):
    #    os.mkdir(os.path.expanduser('~')+os.sep+"docs")
    #workbook = xlsxwriter.Workbook(os.path.expanduser('~')+os.sep+"docs"+os.sep+output_file)

    #workbook = xlsxwriter.Workbook(file_path+os.sep+output_file)
    worksheet = workbook.add_worksheet(sheet_name)
    work_format = workbook.add_format()
    #title = sql_info()[0].split()
    # 标题
    worksheet.write_row('A1', first_row, work_format)
    # 日期
    for i in range(len(all_list)):
        #A的ASCII码为65
        worksheet.write_column(chr(i+65)+"2",all_list[i], work_format)
        #worksheet.write_column(chr(66)+"2",column[1], work_format)
        #worksheet.write_column(chr(67)+"2",column[2], work_format)
    #workbook.close()

def convert(file_path,file_name,output_file):
    dates=date_range(file_path,file_name)
    sql_all=sql_info(file_path,file_name)
    sheet_all=sheet_list(file_path,file_name)
    workbook = xlsxwriter.Workbook(file_path+os.sep+output_file)
    for i in range(len(sql_all)):
        all_list=[]
        for sql in sql_list(sql_all[i],dates):
            all_list.append(getres(sql))
        w_excel(workbook,dates,all_list,file_path,output_file,sheet_all[i])
    workbook.close()

if __name__ == '__main__':
    file_path='D:\\wangwb\\devops\\data\\'
    #file_name='核心交易成功率.txt'
    #output_file='核心交易成功率.xlsx'
    convert(file_path,'电影渠道统计.txt','电影渠道统计.xlsx')
    convert(file_path,'核心交易成功率.txt','核心交易成功率.xlsx')